/**
 * 报表API路由
 * 提供PPH汇总表和异常汇总表功能
 */

const express = require('express');
const router = express.Router();

let dbPool = null;

// 设置数据库连接池
function setDbPool(pool) {
  dbPool = pool;
}

/**
 * GET /api/reports/pph-summary
 * PPH汇总表
 *
 * 分组字段: 记录日期、生产线、订单号、物料编码、产品名称
 * 聚合字段: SUM(完成数量)、AVG(总人数)、SUM(净工作时间)、主打产品、生产类型、实际PPH、目标PPH、预估PPH、达成率
 */
router.get('/pph-summary', async (req, res) => {
  const started = Date.now();
  try {
    if (!dbPool) {
      return res.status(500).json({ success: false, error: 'Database not initialized' });
    }

    const {
      start_date,
      end_date,
      line_name,
      order_number,
      material_code,
      product_name,
      page = 1,
      limit = 50
    } = req.query;

    // 构建WHERE条件
    const whereConditions = [];
    const params = [];

    if (start_date) {
      whereConditions.push('record_date >= ?');
      params.push(start_date);
    }
    if (end_date) {
      whereConditions.push('record_date <= ?');
      params.push(end_date);
    }
    // 支持Excel风格多选：line_name(单选)、line_name[]=a&line_name[]=b、或 line_names=a,b
    const lineNames = Array.isArray(line_name)
      ? line_name.filter(Boolean)
      : (req.query.line_names
          ? String(req.query.line_names).split(',').map(s=>s.trim()).filter(Boolean)
          : (line_name ? [String(line_name)] : []));
    if (lineNames.length > 0) {
      whereConditions.push(`line_name IN (${lineNames.map(()=>'?').join(',')})`);
      params.push(...lineNames);
    }
    if (order_number) {
      whereConditions.push('order_number LIKE ?');
      params.push(`%${order_number}%`);
    }
    if (material_code) {
      whereConditions.push('material_code LIKE ?');
      params.push(`%${material_code}%`);
    }
    if (product_name) {
      whereConditions.push('product_name LIKE ?');
      params.push(`%${product_name}%`);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // PPH汇总查询 - 按照用户要求修正逻辑
    const summaryQuery = `
      SELECT
        record_date,
        line_name,
        order_number,
        material_code,
        product_name,
        SUM(COALESCE(quantity, 0)) as total_quantity,
        AVG(COALESCE(total_people, 0)) as avg_people,
        SUM(COALESCE(net_working_minutes, 0)) as total_minutes,
        MAX(CASE WHEN COALESCE(is_main_product, 0) = 1 THEN 1 ELSE 0 END) as is_main_product,
        MAX(CASE WHEN COALESCE(is_whole_machine, 1) = 1 THEN '整机' ELSE '部件' END) as production_type,
        -- 实际PPH计算：SUM(完成数量) / (SUM(净工作时间)/60 * AVG(总人数))
        CASE
          WHEN SUM(COALESCE(net_working_minutes, 0)) > 0 AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND(SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))), 2)
          ELSE 0
        END as actual_pph,
        -- 平均PPH计算：每条记录PPH的平均值 AVG(单条记录的PPH)
        ROUND(AVG(CASE
          WHEN net_working_minutes > 0 AND total_people > 0
          THEN quantity / (net_working_minutes / 60.0 * total_people)
          ELSE NULL
        END), 2) as average_pph,
        -- 实际节拍（聚合口径，单位：秒/件）
        CASE
          WHEN SUM(COALESCE(quantity,0)) > 0
          THEN ROUND((SUM(COALESCE(net_working_minutes,0)) / SUM(COALESCE(quantity,0))) * 60.0, 2)
          ELSE NULL
        END as actual_takt_seconds,
        -- 目标节拍与整机节拍（取第一个非空值）
        (SELECT target_takt_time FROM pph_records r_t
          WHERE r_t.record_date = r.record_date AND r_t.line_name = r.line_name
            AND r_t.order_number = r.order_number AND r_t.material_code = r.material_code
            AND r_t.target_takt_time IS NOT NULL AND r_t.target_takt_time > 0
          LIMIT 1) as target_takt_time,
        (SELECT target_motor_takt_time FROM pph_records r_m
          WHERE r_m.record_date = r.record_date AND r_m.line_name = r.line_name
            AND r_m.order_number = r.order_number AND r_m.material_code = r.material_code
            AND r_m.target_motor_takt_time IS NOT NULL AND r_m.target_motor_takt_time > 0
          LIMIT 1) as target_motor_takt_time,

        -- 目标PPH：取第一个非空值
        (SELECT target_pph FROM pph_records r2
         WHERE r2.record_date = r.record_date AND r2.line_name = r.line_name
         AND r2.order_number = r.order_number AND r2.material_code = r.material_code
         AND r2.target_pph IS NOT NULL AND r2.target_pph > 0
         LIMIT 1) as target_pph,
        -- 预估PPH：取第一个非空值
        (SELECT estimate_pph FROM pph_records r3
         WHERE r3.record_date = r.record_date AND r3.line_name = r.line_name
         AND r3.order_number = r.order_number AND r3.material_code = r.material_code
         AND r3.estimate_pph IS NOT NULL AND r3.estimate_pph > 0
         LIMIT 1) as estimate_pph,
        -- 达成率计算：优先使用目标PPH，没有则用预估PPH，都没有则为NULL
        CASE
          WHEN (SELECT target_pph FROM pph_records r4
                WHERE r4.record_date = r.record_date AND r4.line_name = r.line_name
                AND r4.order_number = r.order_number AND r4.material_code = r.material_code
                AND r4.target_pph IS NOT NULL AND r4.target_pph > 0
                LIMIT 1) > 0
               AND SUM(COALESCE(net_working_minutes, 0)) > 0
               AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                     / (SELECT target_pph FROM pph_records r5
                        WHERE r5.record_date = r.record_date AND r5.line_name = r.line_name
                        AND r5.order_number = r.order_number AND r5.material_code = r.material_code
                        AND r5.target_pph IS NOT NULL AND r5.target_pph > 0
                        LIMIT 1) * 100, 2)
          WHEN (SELECT estimate_pph FROM pph_records r6
                WHERE r6.record_date = r.record_date AND r6.line_name = r.line_name
                AND r6.order_number = r.order_number AND r6.material_code = r.material_code
                AND r6.estimate_pph IS NOT NULL AND r6.estimate_pph > 0
                LIMIT 1) > 0
               AND SUM(COALESCE(net_working_minutes, 0)) > 0
               AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                     / (SELECT estimate_pph FROM pph_records r7
                        WHERE r7.record_date = r.record_date AND r7.line_name = r.line_name
                        AND r7.order_number = r.order_number AND r7.material_code = r.material_code
                        AND r7.estimate_pph IS NOT NULL AND r7.estimate_pph > 0
                        LIMIT 1) * 100, 2)
          ELSE NULL
        END as efficiency_rate
      FROM pph_records r
      ${whereClause}
      GROUP BY record_date, line_name, order_number, material_code, product_name
      ORDER BY record_date DESC, line_name, order_number
      LIMIT ? OFFSET ?
    `;

    // 计算分页
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const queryParams = [...params, parseInt(limit), offset];

    // 执行查询
    const summaryData = await dbPool.query(summaryQuery, queryParams);

    // 获取总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM (
        SELECT record_date, line_name, order_number, material_code, product_name
        FROM pph_records
        ${whereClause}
        GROUP BY record_date, line_name, order_number, material_code, product_name
      ) grouped
    `;

    const countResult = await dbPool.query(countQuery, params);
    const total = countResult?.[0]?.total || 0;

    // 计算基于查询条件的全量统计数据 - 使用与主查询相同的分组逻辑
    const statsQuery = `
      SELECT
        COUNT(*) as total_records,
        AVG(CASE WHEN calculated_pph > 0 THEN calculated_pph ELSE NULL END) as average_pph,
        AVG(CASE WHEN efficiency_rate IS NOT NULL AND target_pph > 0 THEN efficiency_rate ELSE NULL END) as average_efficiency,
        COUNT(CASE WHEN efficiency_rate >= 90 AND target_pph > 0 THEN 1 ELSE NULL END) * 100.0 /
        NULLIF(COUNT(CASE WHEN target_pph > 0 THEN 1 ELSE NULL END), 0) as achievement_rate
      FROM (
        SELECT
          -- 计算PPH：总产量 / (净工作时间/60 * 总人数)
          ROUND(CASE
            WHEN SUM(COALESCE(net_working_minutes, 0)) > 0 AND AVG(COALESCE(total_people, 0)) > 0
            THEN SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0)))
            ELSE NULL
          END, 2) as calculated_pph,
          -- 目标PPH：取第一个非空值
          (SELECT target_pph FROM pph_records r2
           WHERE r2.record_date = r.record_date AND r2.line_name = r.line_name
           AND r2.order_number = r.order_number AND r2.material_code = r.material_code
           AND r2.target_pph IS NOT NULL AND r2.target_pph > 0
           LIMIT 1) as target_pph,
          -- 达成率计算：优先使用目标PPH，没有则用预估PPH，都没有则为NULL
          CASE
            WHEN (SELECT target_pph FROM pph_records r4
                  WHERE r4.record_date = r.record_date AND r4.line_name = r.line_name
                  AND r4.order_number = r.order_number AND r4.material_code = r.material_code
                  AND r4.target_pph IS NOT NULL AND r4.target_pph > 0
                  LIMIT 1) > 0
                 AND SUM(COALESCE(net_working_minutes, 0)) > 0
                 AND AVG(COALESCE(total_people, 0)) > 0
            THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                       / (SELECT target_pph FROM pph_records r5
                          WHERE r5.record_date = r.record_date AND r5.line_name = r.line_name
                          AND r5.order_number = r.order_number AND r5.material_code = r.material_code
                          AND r5.target_pph IS NOT NULL AND r5.target_pph > 0
                          LIMIT 1) * 100, 2)
            WHEN (SELECT estimate_pph FROM pph_records r6
                  WHERE r6.record_date = r.record_date AND r6.line_name = r.line_name
                  AND r6.order_number = r.order_number AND r6.material_code = r.material_code
                  AND r6.estimate_pph IS NOT NULL AND r6.estimate_pph > 0
                  LIMIT 1) > 0
                 AND SUM(COALESCE(net_working_minutes, 0)) > 0
                 AND AVG(COALESCE(total_people, 0)) > 0
            THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                       / (SELECT estimate_pph FROM pph_records r7
                          WHERE r7.record_date = r.record_date AND r7.line_name = r.line_name
                          AND r7.order_number = r.order_number AND r7.material_code = r.material_code
                          AND r7.estimate_pph IS NOT NULL AND r7.estimate_pph > 0
                          LIMIT 1) * 100, 2)
            ELSE NULL
          END as efficiency_rate
        FROM pph_records r
        ${whereClause}
        GROUP BY r.record_date, r.line_name, r.order_number, r.material_code
      ) grouped_data
    `;

    const statsResult = await dbPool.query(statsQuery, params);
    const stats = statsResult?.[0] || {};

    const cost = Date.now() - started;
    console.log(`[REPORT] /pph-summary ok: ${summaryData?.length || 0} rows, ${total} total, ${cost}ms`);
    res.json({
      success: true,
      data: summaryData || [],
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: total
      },
      statistics: {
        totalRecords: stats.total_records || 0,
        averagePPH: Math.round((stats.average_pph || 0) * 100) / 100,
        averageEfficiency: Math.round((stats.average_efficiency || 0) * 100) / 100,
        achievementRate: Math.round((stats.achievement_rate || 0) * 100) / 100
      }
    });

  } catch (error) {
    console.error('获取PPH汇总数据失败:', error);
    res.status(500).json({
      success: false,
      error: error.message || '获取PPH汇总数据失败'
    });
  }
});

/**
 * GET /api/reports/pph-summary/export
 * PPH汇总表导出Excel
 */
router.get('/pph-summary/export', async (req, res) => {
  try {
    if (!dbPool) {
      return res.status(500).json({ success: false, error: 'Database not initialized' });
    }

    const {
      start_date,
      end_date,
      line_name,
      order_number,
      material_code,
      product_name
    } = req.query;

    // 构建WHERE条件
    const whereConditions = [];
    const params = [];

    if (start_date) {
      whereConditions.push('record_date >= ?');
      params.push(start_date);
    }
    if (end_date) {
      whereConditions.push('record_date <= ?');
      params.push(end_date);
    }
    // 支持Excel风格多选：line_name(单选)、line_name[]=a&line_name[]=b、或 line_names=a,b
    const lineNames = Array.isArray(line_name)
      ? line_name.filter(Boolean)
      : (req.query.line_names
          ? String(req.query.line_names).split(',').map(s=>s.trim()).filter(Boolean)
          : (line_name ? [String(line_name)] : []));
    if (lineNames.length > 0) {
      whereConditions.push(`line_name IN (${lineNames.map(()=>'?').join(',')})`);
      params.push(...lineNames);
    }
    if (order_number) {
      whereConditions.push('order_number LIKE ?');
      params.push(`%${order_number}%`);
    }
    if (material_code) {
      whereConditions.push('material_code LIKE ?');
      params.push(`%${material_code}%`);
    }
    if (product_name) {
      whereConditions.push('product_name LIKE ?');
      params.push(`%${product_name}%`);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // PPH汇总查询（不分页，用于导出）
    const exportQuery = `
      SELECT
        record_date,
        line_name,
        order_number,
        material_code,
        product_name,
        SUM(COALESCE(quantity, 0)) as total_quantity,
        AVG(COALESCE(total_people, 0)) as avg_people,
        SUM(COALESCE(net_working_minutes, 0)) as total_minutes,
        MAX(CASE WHEN COALESCE(is_main_product, 0) = 1 THEN 1 ELSE 0 END) as is_main_product,
        MAX(CASE WHEN COALESCE(is_whole_machine, 1) = 1 THEN '整机' ELSE '部件' END) as production_type,
        CASE
          WHEN SUM(COALESCE(net_working_minutes, 0)) > 0 AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND(SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))), 2)
          ELSE 0
        END as actual_pph,
        -- 平均PPH计算：每条记录PPH的平均值 AVG(单条记录的PPH)
        ROUND(AVG(CASE
          WHEN net_working_minutes > 0 AND total_people > 0
          THEN quantity / (net_working_minutes / 60.0 * total_people)
          ELSE NULL
        END), 2) as average_pph,
        -- 实际节拍（聚合口径，单位：秒/件）
        CASE
          WHEN SUM(COALESCE(quantity,0)) > 0
          THEN ROUND((SUM(COALESCE(net_working_minutes,0)) / SUM(COALESCE(quantity,0))) * 60.0, 2)
          ELSE NULL
        END as actual_takt_seconds,
        -- 目标节拍与整机节拍（取第一个非空值）
        (SELECT target_takt_time FROM pph_records r_t
          WHERE r_t.record_date = r.record_date AND r_t.line_name = r.line_name
            AND r_t.order_number = r.order_number AND r_t.material_code = r.material_code
            AND r_t.target_takt_time IS NOT NULL AND r_t.target_takt_time > 0
          LIMIT 1) as target_takt_time,
        (SELECT target_motor_takt_time FROM pph_records r_m
          WHERE r_m.record_date = r.record_date AND r_m.line_name = r.line_name
            AND r_m.order_number = r.order_number AND r_m.material_code = r.material_code
            AND r_m.target_motor_takt_time IS NOT NULL AND r_m.target_motor_takt_time > 0
          LIMIT 1) as target_motor_takt_time,

        -- 目标PPH：取第一个非空值
        (SELECT target_pph FROM pph_records r2
         WHERE r2.record_date = r.record_date AND r2.line_name = r.line_name
         AND r2.order_number = r.order_number AND r2.material_code = r.material_code
         AND r2.target_pph IS NOT NULL AND r2.target_pph > 0
         LIMIT 1) as target_pph,
        -- 预估PPH：取第一个非空值
        (SELECT estimate_pph FROM pph_records r3
         WHERE r3.record_date = r.record_date AND r3.line_name = r.line_name
         AND r3.order_number = r.order_number AND r3.material_code = r.material_code
         AND r3.estimate_pph IS NOT NULL AND r3.estimate_pph > 0
         LIMIT 1) as estimate_pph,
        -- 达成率计算：优先使用目标PPH，没有则用预估PPH，都没有则为NULL
        CASE
          WHEN (SELECT target_pph FROM pph_records r4
                WHERE r4.record_date = r.record_date AND r4.line_name = r.line_name
                AND r4.order_number = r.order_number AND r4.material_code = r.material_code
                AND r4.target_pph IS NOT NULL AND r4.target_pph > 0
                LIMIT 1) > 0
               AND SUM(COALESCE(net_working_minutes, 0)) > 0
               AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                     / (SELECT target_pph FROM pph_records r5
                        WHERE r5.record_date = r.record_date AND r5.line_name = r.line_name
                        AND r5.order_number = r.order_number AND r5.material_code = r.material_code
                        AND r5.target_pph IS NOT NULL AND r5.target_pph > 0
                        LIMIT 1) * 100, 2)
          WHEN (SELECT estimate_pph FROM pph_records r6
                WHERE r6.record_date = r.record_date AND r6.line_name = r.line_name
                AND r6.order_number = r.order_number AND r6.material_code = r.material_code
                AND r6.estimate_pph IS NOT NULL AND r6.estimate_pph > 0
                LIMIT 1) > 0
               AND SUM(COALESCE(net_working_minutes, 0)) > 0
               AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                     / (SELECT estimate_pph FROM pph_records r7
                        WHERE r7.record_date = r.record_date AND r7.line_name = r.line_name
                        AND r7.order_number = r.order_number AND r7.material_code = r.material_code
                        AND r7.estimate_pph IS NOT NULL AND r7.estimate_pph > 0
                        LIMIT 1) * 100, 2)
          ELSE NULL
        END as efficiency_rate
      FROM pph_records r
      ${whereClause}
      GROUP BY record_date, line_name, order_number, material_code, product_name
      ORDER BY record_date DESC, line_name, order_number
    `;

    // 执行查询
    const exportData = await dbPool.query(exportQuery, params);

    if (!exportData || exportData.length === 0) {
      return res.status(404).json({ error: '没有找到符合条件的数据' });
    }

    // 创建Excel工作簿
    const XLSX = require('xlsx');
    const workbook = XLSX.utils.book_new();

    // 准备导出数据
    const excelData = exportData.map(record => ({
      '记录日期': record.record_date,
      '生产线': record.line_name,
      '订单号': record.order_number,
      '物料编码': record.material_code,
      '产品名称': record.product_name,
      '完成数量': Math.round(record.total_quantity || 0),
      '平均人数': (record.avg_people || 0).toFixed(1),
      '净工作时间(分)': Math.round(record.total_minutes || 0),
      '实际PPH': (record.actual_pph || 0).toFixed(1),
      '平均PPH': record.average_pph ? record.average_pph.toFixed(1) : '-',
      '目标PPH': record.target_pph ? record.target_pph.toFixed(1) : '-',
      '预估PPH': record.estimate_pph ? record.estimate_pph.toFixed(1) : '-',
      '达成率(%)': record.efficiency_rate !== null && record.efficiency_rate !== undefined ? record.efficiency_rate.toFixed(1) : '-',
      '主打产品': record.is_main_product ? '是' : '否',
      '生产类型': record.production_type || ''
    }));

    // 创建工作表
    const worksheet = XLSX.utils.json_to_sheet(excelData);

    // 设置列宽
    const colWidths = [
      { wch: 12 }, // 记录日期
      { wch: 12 }, // 生产线
      { wch: 15 }, // 订单号
      { wch: 15 }, // 物料编码
      { wch: 25 }, // 产品名称
      { wch: 12 }, // 完成数量
      { wch: 12 }, // 平均人数
      { wch: 15 }, // 净工作时间
      { wch: 12 }, // 实际PPH
      { wch: 12 }, // 平均PPH
      { wch: 12 }, // 目标PPH
      { wch: 12 }, // 预估PPH
      { wch: 12 }, // 达成率
      { wch: 12 }, // 主打产品
      { wch: 12 }  // 生产类型
    ];
    worksheet['!cols'] = colWidths;

    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH汇总表');

    // 生成Excel文件
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

    // 设置响应头
    const filename = `PPH汇总表_${new Date().toISOString().split('T')[0]}.xlsx`;
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`);
    res.setHeader('Content-Length', buffer.length);

    console.log(`✅ PPH汇总表导出成功: ${filename}, 记录数: ${exportData.length}`);
    res.send(buffer);

  } catch (error) {
    console.error('❌ PPH汇总表导出失败:', error);
    res.status(500).json({ error: 'PPH汇总表导出失败', details: error.message });
  }
});

/**
 * GET /api/reports/abnormal-summary
 * 异常汇总表
 *
 * 分组字段: 记录日期、生产线、订单号、物料编码、产品名称、异常类别
 * 聚合字段: 异常描述(合并，跳过空行)、异常关闭(优先取"是")
 */
router.get('/abnormal-summary', async (req, res) => {
  const started = Date.now();
  try {
    if (!dbPool) {
      return res.status(500).json({ success: false, error: 'Database not initialized' });
    }

    const {
      start_date,
      end_date,
      line_name,
      order_number,
      material_code,
      product_name,
      abnormality_category,
      abnormality_closed,
      page = 1,
      limit = 50
    } = req.query;

    // 构建WHERE条件
    const whereConditions = ['abnormality_category IS NOT NULL', 'abnormality_category != ""'];
    const params = [];

    if (start_date) {
      whereConditions.push('record_date >= ?');
      params.push(start_date);
    }
    if (end_date) {
      whereConditions.push('record_date <= ?');
      params.push(end_date);
    }
    // 支持Excel风格多选：line_name(单选)、line_name[]=a&line_name[]=b、或 line_names=a,b
    const lineNames = Array.isArray(line_name)
      ? line_name.filter(Boolean)
      : (req.query.line_names
          ? String(req.query.line_names).split(',').map(s=>s.trim()).filter(Boolean)
          : (line_name ? [String(line_name)] : []));
    if (lineNames.length > 0) {
      whereConditions.push(`line_name IN (${lineNames.map(()=>'?').join(',')})`);
      params.push(...lineNames);
    }
    if (order_number) {
      whereConditions.push('order_number LIKE ?');
      params.push(`%${order_number}%`);
    }
    if (material_code) {
      whereConditions.push('material_code LIKE ?');
      params.push(`%${material_code}%`);
    }
    if (product_name) {
      whereConditions.push('product_name LIKE ?');
      params.push(`%${product_name}%`);
    }
    if (abnormality_category) {
      whereConditions.push('abnormality_category LIKE ?');
      params.push(`%${abnormality_category}%`);
    }
    if (abnormality_closed) {
      // 前端传递"是"/"否"，转换为数据库的1/0
      if (abnormality_closed === '是') {
        whereConditions.push('abnormality_closed = 1');
      } else if (abnormality_closed === '否') {
        whereConditions.push('abnormality_closed = 0');
      }
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 异常汇总查询
    const summaryQuery = `
      SELECT
        record_date,
        line_name,
        order_number,
        material_code,
        product_name,
        abnormality_category,
        GROUP_CONCAT(
          CASE
            WHEN abnormality_description IS NOT NULL AND abnormality_description != ''
            THEN abnormality_description
            ELSE NULL
          END,
          '; '
        ) as merged_descriptions,
        MAX(CASE WHEN abnormality_closed = 1 THEN 1 ELSE 0 END) as is_closed,
        COUNT(*) as record_count
      FROM pph_records
      ${whereClause}
      GROUP BY record_date, line_name, order_number, material_code, product_name, abnormality_category
      ORDER BY record_date DESC, line_name, order_number, abnormality_category
      LIMIT ? OFFSET ?
    `;

    // 计算分页
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const queryParams = [...params, parseInt(limit), offset];

    // 执行查询
    const summaryData = await dbPool.query(summaryQuery, queryParams);

    // 转换异常关闭状态：1 -> "是", 0 -> "否"
    const formattedData = (summaryData || []).map(record => ({
      ...record,
      abnormality_closed: record.is_closed === 1 ? '是' : '否'
    }));

    // 获取总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM (
        SELECT record_date, line_name, order_number, material_code, product_name, abnormality_category
        FROM pph_records
        ${whereClause}
        GROUP BY record_date, line_name, order_number, material_code, product_name, abnormality_category
      ) grouped
    `;

    const countResult = await dbPool.query(countQuery, params);
    const total = countResult?.[0]?.total || 0;

    // 计算异常统计数据
    const statsQuery = `
      SELECT
        COUNT(*) as total_abnormal_records,
        COUNT(DISTINCT abnormality_category) as abnormal_categories_count,
        SUM(CASE WHEN abnormality_closed = 1 THEN 1 ELSE 0 END) as closed_count,
        SUM(CASE WHEN abnormality_closed = 0 OR abnormality_closed IS NULL THEN 1 ELSE 0 END) as unclosed_count
      FROM pph_records
      ${whereClause}
    `;

    const statsResult = await dbPool.query(statsQuery, params);
    const stats = statsResult?.[0] || {};

    const cost = Date.now() - started;
    console.log(`[REPORT] /abnormal-summary ok: ${formattedData?.length || 0} rows, ${total} total, ${cost}ms`);
    res.json({
      success: true,
      data: formattedData,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: total
      },
      statistics: {
        totalAbnormalRecords: stats.total_abnormal_records || 0,
        abnormalCategoriesCount: stats.abnormal_categories_count || 0,
        closedCount: stats.closed_count || 0,
        unclosedCount: stats.unclosed_count || 0
      }
    });

  } catch (error) {
    console.error('获取异常汇总数据失败:', error);
    res.status(500).json({
      success: false,
      error: error.message || '获取异常汇总数据失败'
    });
  }
});

/**
 * GET /api/reports/abnormal-summary/export
 * 异常汇总表导出Excel
 */
router.get('/abnormal-summary/export', async (req, res) => {
  try {
    if (!dbPool) {
      return res.status(500).json({ success: false, error: 'Database not initialized' });
    }

    const {
      start_date,
      end_date,
      line_name,
      order_number,
      material_code,
      product_name,
      abnormality_category,
      abnormality_closed
    } = req.query;

    // 构建WHERE条件（与主查询保持一致）
    const whereConditions = ['abnormality_category IS NOT NULL', 'abnormality_category != ""'];
    const params = [];

    if (start_date) {
      whereConditions.push('record_date >= ?');
      params.push(start_date);
    }
    if (end_date) {
      whereConditions.push('record_date <= ?');
      params.push(end_date);
    }
    // 支持Excel风格多选：line_name(单选)、line_name[]=a&line_name[]=b、或 line_names=a,b
    const lineNames = Array.isArray(line_name)
      ? line_name.filter(Boolean)
      : (req.query.line_names
          ? String(req.query.line_names).split(',').map(s=>s.trim()).filter(Boolean)
          : (line_name ? [String(line_name)] : []));
    if (lineNames.length > 0) {
      whereConditions.push(`line_name IN (${lineNames.map(()=>'?').join(',')})`);
      params.push(...lineNames);
    }
    if (order_number) {
      whereConditions.push('order_number LIKE ?');
      params.push(`%${order_number}%`);
    }
    if (material_code) {
      whereConditions.push('material_code LIKE ?');
      params.push(`%${material_code}%`);
    }
    if (product_name) {
      whereConditions.push('product_name LIKE ?');
      params.push(`%${product_name}%`);
    }
    if (abnormality_category) {
      whereConditions.push('abnormality_category LIKE ?');
      params.push(`%${abnormality_category}%`);
    }
    if (abnormality_closed) {
      // 前端传递"是"/"否"，转换为数据库的1/0
      if (abnormality_closed === '是') {
        whereConditions.push('abnormality_closed = 1');
      } else if (abnormality_closed === '否') {
        whereConditions.push('abnormality_closed = 0');
      }
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 异常汇总查询（不分页，用于导出）
    const exportQuery = `
      SELECT
        record_date,
        line_name,
        order_number,
        material_code,
        product_name,
        abnormality_category,
        GROUP_CONCAT(
          CASE
            WHEN abnormality_description IS NOT NULL AND abnormality_description != ''
            THEN abnormality_description
            ELSE NULL
          END,
          '; '
        ) as merged_descriptions,
        MAX(CASE WHEN abnormality_closed = 1 THEN 1 ELSE 0 END) as is_closed,
        COUNT(*) as record_count
      FROM pph_records
      ${whereClause}
      GROUP BY record_date, line_name, order_number, material_code, product_name, abnormality_category
      ORDER BY record_date DESC, line_name, order_number, abnormality_category
    `;

    // 执行查询
    const exportData = await dbPool.query(exportQuery, params);

    if (!exportData || exportData.length === 0) {
      return res.status(404).json({ error: '没有找到符合条件的数据' });
    }

    // 转换异常关闭状态：1 -> "是", 0 -> "否"
    const formattedData = exportData.map(record => ({
      ...record,
      abnormality_closed: record.is_closed === 1 ? '是' : '否'
    }));

    // 创建Excel工作簿
    const XLSX = require('xlsx');
    const workbook = XLSX.utils.book_new();

    // 准备导出数据
    const exportRows = formattedData.map(record => ({
      '记录日期': record.record_date,
      '生产线': record.line_name,
      '订单号': record.order_number,
      '物料编码': record.material_code,
      '产品名称': record.product_name,
      '异常类别': record.abnormality_category,
      '异常描述': record.merged_descriptions || '-',
      '异常关闭': record.abnormality_closed,
      '记录数量': record.record_count
    }));

    // 创建工作表
    const worksheet = XLSX.utils.json_to_sheet(exportRows);

    // 设置列宽
    const colWidths = [
      { wch: 12 }, // 记录日期
      { wch: 12 }, // 生产线
      { wch: 15 }, // 订单号
      { wch: 15 }, // 物料编码
      { wch: 25 }, // 产品名称
      { wch: 15 }, // 异常类别
      { wch: 40 }, // 异常描述
      { wch: 12 }, // 异常关闭
      { wch: 12 }  // 记录数量
    ];
    worksheet['!cols'] = colWidths;

    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, worksheet, '异常汇总表');

    // 生成Excel文件
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

    // 设置响应头
    const filename = `异常汇总表_${new Date().toISOString().split('T')[0]}.xlsx`;
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`);
    res.setHeader('Content-Length', buffer.length);

    console.log(`✅ 异常汇总表导出成功: ${filename}, 记录数: ${exportData.length}`);
    res.send(buffer);

  } catch (error) {
    console.error('❌ 异常汇总表导出失败:', error);
    res.status(500).json({ error: '异常汇总表导出失败', details: error.message });
  }
});


// —— 周/月/年周期计算（放在路由前，确保可用） ——
function getWeeksByDateRange(startDate, endDate) {
  const weeks = [];
  let weekNumber = 1;
  const rangeStart = new Date(startDate);
  const rangeEnd = new Date(endDate);
  // 找到包含起始日期的那一周的周一
  let currentMonday = new Date(rangeStart);
  const dayOfWeek = rangeStart.getDay();
  const daysToMonday = dayOfWeek === 0 ? 6 : dayOfWeek - 1;
  currentMonday.setDate(rangeStart.getDate() - daysToMonday);
  while (currentMonday <= rangeEnd) {
    const weekStart = new Date(currentMonday);
    const weekEnd = new Date(currentMonday);
    weekEnd.setDate(weekEnd.getDate() + 6);
    const overlapStart = weekStart < rangeStart ? rangeStart : weekStart;
    const overlapEnd = weekEnd > rangeEnd ? rangeEnd : weekEnd;
    const overlapDays = Math.max(0, Math.floor((overlapEnd - overlapStart) / (1000 * 60 * 60 * 24)) + 1);
    if (overlapDays > 0) {
      weeks.push({ week: weekNumber, startDate: weekStart.toISOString().split('T')[0], endDate: weekEnd.toISOString().split('T')[0], overlapDays });
    }
    currentMonday.setDate(currentMonday.getDate() + 7);
    weekNumber++;
    if (weekNumber > 200) break;
  }
  return weeks;
}

function getMonthRangesByDate(startDate, endDate) {
  const ranges = [];
  const start = new Date(startDate);
  const end = new Date(endDate);
  let y = start.getFullYear();
  let m = start.getMonth();
  const endY = end.getFullYear();
  const endM = end.getMonth();
  while (y < endY || (y === endY && m <= endM)) {
    const first = new Date(y, m, 1);
    const last = new Date(y, m + 1, 0);
    ranges.push({ period: `${y}-${String(m + 1).padStart(2, '0')}`, startDate: first.toISOString().split('T')[0], endDate: last.toISOString().split('T')[0] });
    m += 1; if (m > 11) { m = 0; y += 1; }
  }
  return ranges;
}

function getYearRangesByDate(startDate, endDate) {
  const ranges = [];
  const start = new Date(startDate);
  const end = new Date(endDate);
  let y = start.getFullYear();
  const endY = end.getFullYear();
  while (y <= endY) {
    const first = new Date(y, 0, 1);
    const last = new Date(y, 11, 31);
    ranges.push({ period: `${y}`, startDate: first.toISOString().split('T')[0], endDate: last.toISOString().split('T')[0] });
    y += 1;
  }
  return ranges;
}

// 兼容旧函数名（别名）
const calculateDateRangeWeeks = getWeeksByDateRange;
const calculateMonthRangesByDate = getMonthRangesByDate;
const calculateYearRangesByDate = getYearRangesByDate;


/**
 * GET /api/reports/abnormal-weekly
 * 异常汇总（周）视图
 *
 * 按周统计异常类别，支持按生产线分别统计
 */
router.get('/abnormal-weekly', async (req, res) => {
  try {
    if (!dbPool) {
      return res.status(500).json({ success: false, error: 'Database not initialized' });
    }

    const {
      start_year = new Date().getFullYear(),
      end_year = new Date().getFullYear(),
      start_month = 1,
      end_month = 12,
      line_name,
      abnormality_category,
      abnormality_closed,
      preset,
      granularity
    } = req.query;

    // 计算时间范围（支持 preset 快捷选择 和 granularity 粒度）
    let startYear = parseInt(start_year);
    let endYear = parseInt(end_year);
    let startMonth = parseInt(start_month);
    let endMonth = parseInt(end_month);

    const now = new Date();
    function fmt(d) { return new Date(d.getTime() - d.getTimezoneOffset()*60000).toISOString().split('T')[0]; }

    // 解析 preset 并确定日期窗口（周 preset 使用精确周一~周日日期；月/年使用边界）
    let presetStartDate = null;
    let presetEndDate = null;
    if (preset) {
      switch (preset) {
        case 'this_week': {
          const day = now.getDay();
          const diffToMon = day === 0 ? 6 : day - 1;
          const mon = new Date(now); mon.setDate(now.getDate() - diffToMon);
          const sun = new Date(mon); sun.setDate(mon.getDate() + 6);
          presetStartDate = fmt(mon);
          presetEndDate = fmt(sun);
          break;
        }
        case 'last_week': {
          const day = now.getDay();
          const diffToMon = day === 0 ? 6 : day - 1;
          const thisMon = new Date(now); thisMon.setDate(now.getDate() - diffToMon);
          const lastMon = new Date(thisMon); lastMon.setDate(thisMon.getDate() - 7);
          const lastSun = new Date(lastMon); lastSun.setDate(lastMon.getDate() + 6);
          presetStartDate = fmt(lastMon);
          presetEndDate = fmt(lastSun);
          break;
        }
        case 'this_month': {
          startYear = now.getFullYear(); endYear = now.getFullYear();
          startMonth = now.getMonth() + 1; endMonth = startMonth; break;
        }
        case 'last_month': {
          const d = new Date(now.getFullYear(), now.getMonth(), 1); d.setMonth(d.getMonth() - 1);
          startYear = d.getFullYear(); endYear = d.getFullYear();
          startMonth = d.getMonth() + 1; endMonth = startMonth; break;
        }
        case 'this_year': {
          startYear = now.getFullYear(); endYear = startYear; startMonth = 1; endMonth = 12; break;
        }
        case 'last_year': {
          startYear = now.getFullYear() - 1; endYear = startYear; startMonth = 1; endMonth = 12; break;
        }
      }
    }

    let gran = (granularity || '').toString().toLowerCase();
    if (!gran) {
      if (preset === 'this_month' || preset === 'last_month') gran = 'month';
      else if (preset === 'this_year' || preset === 'last_year') gran = 'year';
      else gran = 'week';
    }

    // 计算起始和结束日期
    const startDate = (presetStartDate && presetEndDate)
      ? presetStartDate
      : `${startYear}-${String(startMonth).padStart(2, '0')}-01`;
    const endDate = (presetStartDate && presetEndDate)
      ? presetEndDate
      : new Date(endYear, endMonth, 0).toISOString().split('T')[0];

    // 计算范围内的周期
    const periodRanges = (gran === 'week')
      ? getWeeksByDateRange(startDate, endDate)
      : (gran === 'month')
        ? getMonthRangesByDate(startDate, endDate)
        : getYearRangesByDate(startDate, endDate);


    console.log('🔍 异常汇总周期范围:', { startDate, endDate, granularity: gran, periodCount: periodRanges.length });

    // 构建WHERE条件
    const whereConditions = ['abnormality_category IS NOT NULL', 'abnormality_category != ""'];
    const params = [];

    // 添加时间范围条件
    whereConditions.push('record_date >= ?');
    whereConditions.push('record_date <= ?');
    params.push(startDate);
    params.push(endDate);

    // 按用户要求：周期汇总不区分生产线，忽略 line_name 过滤
    if (abnormality_category) {
      whereConditions.push('abnormality_category LIKE ?');
      params.push(`%${abnormality_category}%`);
    }
    if (abnormality_closed) {
      // 前端传递"是"/"否"，转换为数据库的1/0
      if (abnormality_closed === '是') {
        whereConditions.push('abnormality_closed = 1');
      } else if (abnormality_closed === '否') {
        whereConditions.push('abnormality_closed = 0');
      }
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 异常周汇总查询 - 返回原始记录，按周在应用层聚合
    const weeklyQuery = `
      SELECT
        record_date,
        abnormality_category,
        abnormality_closed
      FROM pph_records
      ${whereClause}
      ORDER BY record_date, abnormality_category
    `;

    console.log('🔍 异常汇总（周）查询SQL:', weeklyQuery);
    console.log('🔍 查询参数:', params);

    const rawData = await dbPool.query(weeklyQuery, params);

    // 按周期分组并按 生产线+异常类别 汇总出现次数
    const groupedData = periodRanges.map(period => {
      const pStart = new Date(period.startDate);
      const pEnd = new Date(period.endDate);

      const recordsInPeriod = (rawData || []).filter(r => {
        const d = new Date(r.record_date);
        return d >= pStart && d <= pEnd;
      });

      const summary = {};
      recordsInPeriod.forEach(r => {
        const key = r.abnormality_category || '未分类';
        if (!summary[key]) {
          summary[key] = {
            abnormality_category: r.abnormality_category || '未分类',
            occurrence_count: 0,
            closed_count: 0,
            close_rate: 0
          };
        }
        summary[key].occurrence_count += 1;
        summary[key].closed_count += (r.abnormality_closed === 1 ? 1 : 0);
      });

      Object.values(summary).forEach((item) => {
        if (item.occurrence_count > 0) {
          item.close_rate = Math.round((item.closed_count * 1000.0 / item.occurrence_count)) / 10;
        }
      });

      const payload = {
        period: (period.period || period.week?.toString()),
        periodLabel: period.period ? period.period : `第${period.week}周 (${period.startDate} ~ ${period.endDate})`,
        startDate: period.startDate,
        endDate: period.endDate,
        data: Object.values(summary)
      };
      if (period.week !== undefined) {
        payload.week = period.week;
        payload.weekLabel = `第${period.week}周 (${period.startDate} ~ ${period.endDate})`;
      }
      return payload;
    });

    // 统计数据
    const totalAbnormalRecords = (rawData || []).length;
    const abnormalCategoriesCount = new Set((rawData || []).map(r => r.abnormality_category)).size;
    const closedCount = (rawData || []).reduce((sum, r) => sum + (r.abnormality_closed === 1 ? 1 : 0), 0);
    const unclosedCount = totalAbnormalRecords - closedCount;

    res.json({
      success: true,
      granularity: gran,
      start_year: startYear,
      end_year: endYear,
      start_month: startMonth,
      end_month: endMonth,
      date_range: { startDate, endDate },
      periodRanges,
      weekRanges: (gran === 'week') ? periodRanges : [],
      data: groupedData,
      statistics: {
        totalAbnormalRecords: totalAbnormalRecords,
        abnormalCategoriesCount: abnormalCategoriesCount,
        closedCount: closedCount,
        unclosedCount: unclosedCount
      }
    });

  } catch (error) {
    console.error('获取异常周汇总数据失败:', error);
    res.status(500).json({
      success: false,
      error: error.message || '获取异常周汇总数据失败'
    });
  }
});

/**
 * 计算指定月份的周范围
 * 按照ISO 8601标准（周一为一周开始），跨月按大部分天数归属
 */
function calculateMonthWeeks(year, month) {
  const weeks = [];
  const firstDay = new Date(year, month - 1, 1);
  const lastDay = new Date(year, month, 0);

  // 找到包含1号的那一周的周一
  let currentMonday = new Date(firstDay);
  const dayOfWeek = firstDay.getDay();
  const daysToMonday = dayOfWeek === 0 ? 6 : dayOfWeek - 1; // 周日=0，需要往前6天；其他往前(dayOfWeek-1)天
  currentMonday.setDate(firstDay.getDate() - daysToMonday);

  let weekNumber = 1;

  while (weekNumber <= 5) { // 最多5周
    const weekStart = new Date(currentMonday);
    const weekEnd = new Date(currentMonday);
    weekEnd.setDate(weekEnd.getDate() + 6); // 周日

    // 检查这一周是否与当前月份有交集
    const monthStart = new Date(year, month - 1, 1);
    const monthEnd = new Date(year, month, 0);

    if (weekStart > monthEnd) {
      break; // 这一周完全在下个月，停止
    }

    // 计算这一周在当前月份的天数
    const overlapStart = weekStart < monthStart ? monthStart : weekStart;
    const overlapEnd = weekEnd > monthEnd ? monthEnd : weekEnd;
    const overlapDays = Math.max(0, Math.floor((overlapEnd - overlapStart) / (1000 * 60 * 60 * 24)) + 1);

    // 如果这一周大部分天数在当前月份（>=4天），则归属当前月份
    if (overlapDays >= 4 || (weekNumber === 1 && overlapDays > 0)) {
      weeks.push({
        week: weekNumber,
        startDate: weekStart.toISOString().split('T')[0],
        endDate: weekEnd.toISOString().split('T')[0],
        overlapDays
      });
    }

    // 移动到下一周
    currentMonday.setDate(currentMonday.getDate() + 7);
    weekNumber++;
  }

  return weeks;
}

/**
 * 计算指定年份的所有周范围
 * @param {number} year - 年份
 * @returns {Array} 周范围数组
 */
function calculateYearWeeks(year) {
  const weeks = [];
  let weekNumber = 1;

  // 从年初开始
  const yearStart = new Date(year, 0, 1);
  const yearEnd = new Date(year, 11, 31);

  // 找到包含1月1日的那一周的周一
  let currentMonday = new Date(yearStart);
  const dayOfWeek = yearStart.getDay();
  const daysToMonday = dayOfWeek === 0 ? 6 : dayOfWeek - 1;
  currentMonday.setDate(yearStart.getDate() - daysToMonday);

  while (currentMonday <= yearEnd) {
    const weekStart = new Date(currentMonday);
    const weekEnd = new Date(currentMonday);
    weekEnd.setDate(weekEnd.getDate() + 6);

    // 计算这一周在当前年份的天数
    const overlapStart = weekStart < yearStart ? yearStart : weekStart;
    const overlapEnd = weekEnd > yearEnd ? yearEnd : weekEnd;
    const overlapDays = Math.max(0, Math.floor((overlapEnd - overlapStart) / (1000 * 60 * 60 * 24)) + 1);

    // 如果这一周大部分天数在当前年份（>=4天），则归属当前年份
    if (overlapDays >= 4 || (weekNumber === 1 && overlapDays > 0)) {
      weeks.push({
        week: weekNumber,
        startDate: weekStart.toISOString().split('T')[0],
        endDate: weekEnd.toISOString().split('T')[0],
        overlapDays
      });
    }


  // 移动到下一周
  currentMonday.setDate(currentMonday.getDate() + 7);
  weekNumber++;

  // 防止无限循环
  if (weekNumber > 54) break;
}

return weeks;
}



/**
 * GET /api/reports/efficiency-analysis
 * 达标率分析：总体/按生产线，粒度=周|月|年
 * 说明：达标率只统计有目标PPH(target_pph>0)的分组；阈值为 efficiency_rate>=threshold_percent
 */
router.get('/efficiency-analysis', async (req, res) => {
  const started = Date.now();
  try {
    if (!dbPool) return res.status(500).json({ success: false, error: 'Database not initialized' });

    const now = new Date();
    const fmt = (d) => `${d.getFullYear()}-${String(d.getMonth()+1).padStart(2,'0')}-${String(d.getDate()).padStart(2,'0')}`;

    let {
      preset = '',
      granularity = '',
      dimension = 'overall', // overall | line
      threshold_percent = 95,
      start_year = now.getFullYear(),
      end_year = now.getFullYear(),
      start_month = 1,
      end_month = 12,
      start_date = '',
      end_date = '',
      metric = 'pass_rate', // pass_rate | avg_efficiency
      line_names = ''
    } = req.query;

    threshold_percent = Number(threshold_percent) || 95;
    let presetStartDate = null, presetEndDate = null;

    if (preset) {
      switch (preset) {
        case 'this_week': {
          const day = now.getDay(); const diff = day===0?6:day-1;
          const mon = new Date(now); mon.setDate(now.getDate()-diff);
          const sun = new Date(mon); sun.setDate(mon.getDate()+6);
          presetStartDate = fmt(mon); presetEndDate = fmt(sun); break;
        }
        case 'last_week': {
          const day = now.getDay(); const diff = day===0?6:day-1;
          const thisMon = new Date(now); thisMon.setDate(now.getDate()-diff);
          const lastMon = new Date(thisMon); lastMon.setDate(thisMon.getDate()-7);
          const lastSun = new Date(lastMon); lastSun.setDate(lastMon.getDate()+6);
          presetStartDate = fmt(lastMon); presetEndDate = fmt(lastSun); break;
        }
        case 'this_month': {
          const first = new Date(now.getFullYear(), now.getMonth(), 1);
          const last = new Date(now.getFullYear(), now.getMonth()+1, 0);
          presetStartDate = fmt(first); presetEndDate = fmt(last); break;
        }
        case 'last_month': {
          const first = new Date(now.getFullYear(), now.getMonth()-1, 1);
          const last = new Date(now.getFullYear(), now.getMonth(), 0);
          presetStartDate = fmt(first); presetEndDate = fmt(last); break;
        }
        case 'this_year': {
          presetStartDate = `${now.getFullYear()}-01-01`; presetEndDate = `${now.getFullYear()}-12-31`; break;
        }
        case 'last_year': {
          const y = now.getFullYear()-1; presetStartDate = `${y}-01-01`; presetEndDate = `${y}-12-31`; break;
        }
      }
    }

    let gran = (granularity||'').toString().toLowerCase();
    if (!gran) {
      if (preset==='this_month'||preset==='last_month') gran='month';
      else if (preset==='this_year'||preset==='last_year') gran='year';
      else gran='week';
    }

    const startDate = (start_date && String(start_date)) || presetStartDate || `${start_year}-${String(start_month).padStart(2,'0')}-01`;
    const endDate = (end_date && String(end_date)) || presetEndDate || new Date(end_year, end_month, 0).toISOString().split('T')[0];

    // 取基础分组数据（与现有口径一致）
    const baseQuery = `
      SELECT
        r.record_date,
        r.line_name,
        -- 实际PPH（分组合并口径）
        ROUND(CASE
          WHEN SUM(COALESCE(net_working_minutes, 0)) > 0 AND AVG(COALESCE(total_people, 0)) > 0
          THEN SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0)))
          ELSE NULL
        END, 2) as calculated_pph,
        -- 目标PPH与预估PPH（取首个非空值）
        (SELECT target_pph FROM pph_records r2
         WHERE r2.record_date = r.record_date AND r2.line_name = r.line_name
           AND r2.order_number = r.order_number AND r2.material_code = r.material_code
           AND r2.target_pph IS NOT NULL AND r2.target_pph > 0
         LIMIT 1) as target_pph,
        (SELECT estimate_pph FROM pph_records r3
         WHERE r3.record_date = r.record_date AND r3.line_name = r.line_name
           AND r3.order_number = r.order_number AND r3.material_code = r.material_code
           AND r3.estimate_pph IS NOT NULL AND r3.estimate_pph > 0
         LIMIT 1) as estimate_pph,
        CASE
          WHEN (SELECT target_pph FROM pph_records r4
                WHERE r4.record_date = r.record_date AND r4.line_name = r.line_name
                  AND r4.order_number = r.order_number AND r4.material_code = r.material_code
                  AND r4.target_pph IS NOT NULL AND r4.target_pph > 0
                LIMIT 1) > 0
               AND SUM(COALESCE(net_working_minutes, 0)) > 0 AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                     / (SELECT target_pph FROM pph_records r5
                        WHERE r5.record_date = r.record_date AND r5.line_name = r.line_name
                          AND r5.order_number = r.order_number AND r5.material_code = r.material_code
                          AND r5.target_pph IS NOT NULL AND r5.target_pph > 0
                        LIMIT 1) * 100, 2)
          WHEN (SELECT estimate_pph FROM pph_records r6
                WHERE r6.record_date = r.record_date AND r6.line_name = r.line_name
                  AND r6.order_number = r.order_number AND r6.material_code = r.material_code
                  AND r6.estimate_pph IS NOT NULL AND r6.estimate_pph > 0
                LIMIT 1) > 0
               AND SUM(COALESCE(net_working_minutes, 0)) > 0 AND AVG(COALESCE(total_people, 0)) > 0
          THEN ROUND((SUM(COALESCE(quantity, 0)) / (SUM(COALESCE(net_working_minutes, 0)) / 60.0 * AVG(COALESCE(total_people, 0))))
                     / (SELECT estimate_pph FROM pph_records r7
                        WHERE r7.record_date = r.record_date AND r7.line_name = r.line_name
                          AND r7.order_number = r.order_number AND r7.material_code = r.material_code
                          AND r7.estimate_pph IS NOT NULL AND r7.estimate_pph > 0
                        LIMIT 1) * 100, 2)
          ELSE NULL
        END as efficiency_rate
      FROM pph_records r
      WHERE r.record_date >= ? AND r.record_date <= ?
      GROUP BY r.record_date, r.line_name, r.order_number, r.material_code
      ORDER BY r.record_date, r.line_name
    `;

    const baseRows = await dbPool.query(baseQuery, [startDate, endDate]);

    // 构造周期
    let periods = [];
    if (gran==='week') periods = getWeeksByDateRange(startDate, endDate).map(w=>({ period:`W${w.week}`, startDate:w.startDate, endDate:w.endDate }));
    else if (gran==='month') periods = getMonthRangesByDate(startDate, endDate);
    else periods = getYearRangesByDate(startDate, endDate);

    const selectedLines = (line_names ? String(line_names).split(',').map(s=>s.trim()).filter(Boolean) : null);

    const result = [];
    for (const p of periods) {
      let rowsIn = baseRows.filter(r => r.record_date >= p.startDate && r.record_date <= p.endDate);
      // 生产线过滤（如果传了 line_names）
      if (selectedLines && selectedLines.length) {
        rowsIn = rowsIn.filter(r => selectedLines.includes(String(r.line_name||'')));
      }

      if (dimension === 'line') {
        // 按生产线
        const byLine = new Map();
        for (const r of rowsIn) {
          const ln = r.line_name || '未知产线';
          if (!byLine.has(ln)) byLine.set(ln, []);
          byLine.get(ln).push(r);
        }
        const data = [];
        byLine.forEach((list, ln) => {
          const valid = list.filter(x => Number(x.target_pph) > 0 && x.efficiency_rate != null);
          const total_groups = list.length;
          const covered_groups = valid.length; // 有目标PPH的分组数
          const pass = valid.filter(x => Number(x.efficiency_rate) >= threshold_percent).length;
          const pass_rate = covered_groups>0 ? Math.round((pass*10000/covered_groups))/100 : 0;
          const coverage = total_groups>0 ? Math.round((covered_groups*10000/total_groups))/100 : 0;
          // 平均达成率（算术平均）
          const avg_efficiency = covered_groups>0 ? Math.round((valid.reduce((s,x)=>s+Number(x.efficiency_rate||0),0)*100/covered_groups))/100 : 0;
          data.push({ period: p.period, line_name: ln, total_groups, covered_groups, coverage, pass_records: pass, pass_rate, avg_efficiency });
        });
        result.push({ period: p.period, startDate: p.startDate, endDate: p.endDate, data });
      } else {
        // 总体
        const total_groups = rowsIn.length;
        const valid = rowsIn.filter(x => Number(x.target_pph) > 0 && x.efficiency_rate != null);
        const covered_groups = valid.length;
        const pass = valid.filter(x => Number(x.efficiency_rate) >= threshold_percent).length;
        const pass_rate = covered_groups>0 ? Math.round((pass*10000/covered_groups))/100 : 0;
        const coverage = total_groups>0 ? Math.round((covered_groups*10000/total_groups))/100 : 0;
        const avg_efficiency = covered_groups>0 ? Math.round((valid.reduce((s,x)=>s+Number(x.efficiency_rate||0),0)*100/covered_groups))/100 : 0;
        result.push({ period: p.period, startDate: p.startDate, endDate: p.endDate, data: [{ total_groups, covered_groups, coverage, pass_records: pass, pass_rate, avg_efficiency }] });
      }
    }

    const totals = result.reduce((acc, p) => {
      const list = p.data || [];
      if (dimension==='line') {
        acc.total_groups += list.reduce((s,x)=>s+(x.total_groups||0),0);
        acc.covered_groups += list.reduce((s,x)=>s+(x.covered_groups||0),0);
        acc.pass_records += list.reduce((s,x)=>s+(x.pass_records||0),0);
      } else {
        const item = list[0] || { total_groups:0, covered_groups:0, pass_records:0 };
        acc.total_groups += item.total_groups; acc.covered_groups += item.covered_groups; acc.pass_records += item.pass_records;
      }
      return acc;
    }, { total_groups:0, covered_groups:0, pass_records:0 });

    const resp = {
      success: true,
      granularity: gran,
      dimension,
      threshold_percent,
      date_range: { startDate, endDate },
      statistics: {
        total_groups: totals.total_groups,
        covered_groups: totals.covered_groups,
        coverage: totals.total_groups>0 ? Math.round((totals.covered_groups*10000/totals.total_groups))/100 : 0,
        pass_records: totals.pass_records,
        pass_rate: totals.covered_groups>0 ? Math.round((totals.pass_records*10000/totals.covered_groups))/100 : 0,
        avg_efficiency: (function(){
          const allValid = [];
          for (const p of result) {
            for (const it of (p.data||[])) {
              if (it && typeof it==='object') {
                allValid.push({covered: it.covered_groups||0, avg: it.avg_efficiency||0});
              }
            }
          }
          const sumCovered = allValid.reduce((s,x)=>s+x.covered,0);
          if (!sumCovered) return 0;
          const totalSum = allValid.reduce((s,x)=>s + x.covered * x.avg, 0);
          return Math.round(totalSum*100/sumCovered)/100;
        })()
      },
      data: result,
      metric: metric || 'pass_rate'
    };

    res.json(resp);
  } catch (error) {
    console.error('efficiency-analysis error:', error);
    res.status(500).json({ success: false, error: error.message, stack: String(error.stack||'') });
  } finally {
    const ms = Date.now()-started;
    console.log(`[efficiency-analysis] ${ms}ms`);
  }
});

/**
 * GET /api/reports/efficiency-analysis/export
 * 达标率分析导出：与 /efficiency-analysis 同步口径，导出为 XLSX
 */
router.get('/efficiency-analysis/export', async (req, res) => {
  const started = Date.now();
  try {
    if (!dbPool) return res.status(500).json({ success: false, error: 'Database not initialized' });

    const now = new Date();
    const fmt = (d) => `${d.getFullYear()}-${String(d.getMonth()+1).padStart(2,'0')}-${String(d.getDate()).padStart(2,'0')}`;

    let {
      preset = '',
      granularity = '',
      dimension = 'overall',
      threshold_percent = 95,
      start_year = now.getFullYear(),
      end_year = now.getFullYear(),
      start_month = 1,
      end_month = 12,
      start_date = '',
      end_date = '',
      metric = 'pass_rate',
      line_names = ''
    } = req.query;

    threshold_percent = Number(threshold_percent) || 95;

    let presetStartDate = null, presetEndDate = null;
    if (preset) {
      switch (preset) {
        case 'this_week': { const day=now.getDay(); const diff=day===0?6:day-1; const mon=new Date(now); mon.setDate(now.getDate()-diff); const sun=new Date(mon); sun.setDate(mon.getDate()+6); presetStartDate=fmt(mon); presetEndDate=fmt(sun); break; }
        case 'last_week': { const day=now.getDay(); const diff=day===0?6:day-1; const thisMon=new Date(now); thisMon.setDate(now.getDate()-diff); const lastMon=new Date(thisMon); lastMon.setDate(thisMon.getDate()-7); const lastSun=new Date(lastMon); lastSun.setDate(lastSun.getDate()+6); presetStartDate=fmt(lastMon); presetEndDate=fmt(lastSun); break; }
        case 'this_month': { const first=new Date(now.getFullYear(), now.getMonth(), 1); const last=new Date(now.getFullYear(), now.getMonth()+1, 0); presetStartDate=fmt(first); presetEndDate=fmt(last); break; }
        case 'last_month': { const first=new Date(now.getFullYear(), now.getMonth()-1, 1); const last=new Date(now.getFullYear(), now.getMonth(), 0); presetStartDate=fmt(first); presetEndDate=fmt(last); break; }
        case 'this_year': { presetStartDate=`${now.getFullYear()}-01-01`; presetEndDate=`${now.getFullYear()}-12-31`; break; }
        case 'last_year': { const y=now.getFullYear()-1; presetStartDate=`${y}-01-01`; presetEndDate=`${y}-12-31`; break; }
      }
    }

    let gran = (granularity||'').toString().toLowerCase();
    if (!gran) { if (preset==='this_month'||preset==='last_month') gran='month'; else if (preset==='this_year'||preset==='last_year') gran='year'; else gran='week'; }

    const startDate = (start_date && String(start_date)) || presetStartDate || `${start_year}-${String(start_month).padStart(2,'0')}-01`;
    const endDate = (end_date && String(end_date)) || presetEndDate || new Date(end_year, end_month, 0).toISOString().split('T')[0];

    const baseQuery = `
      SELECT r.record_date, r.line_name,
        ROUND(CASE WHEN SUM(COALESCE(net_working_minutes,0))>0 AND AVG(COALESCE(total_people,0))>0
          THEN SUM(COALESCE(quantity,0)) / (SUM(COALESCE(net_working_minutes,0))/60.0 * AVG(COALESCE(total_people,0)))
          ELSE NULL END, 2) AS calculated_pph,
        (SELECT target_pph FROM pph_records r2 WHERE r2.record_date=r.record_date AND r2.line_name=r.line_name AND r2.order_number=r.order_number AND r2.material_code=r.material_code AND r2.target_pph IS NOT NULL AND r2.target_pph>0 LIMIT 1) AS target_pph,
        (SELECT estimate_pph FROM pph_records r3 WHERE r3.record_date=r.record_date AND r3.line_name=r.line_name AND r3.order_number=r.order_number AND r3.material_code=r.material_code AND r3.estimate_pph IS NOT NULL AND r3.estimate_pph>0 LIMIT 1) AS estimate_pph,
        CASE
          WHEN (SELECT target_pph FROM pph_records r4 WHERE r4.record_date=r.record_date AND r4.line_name=r.line_name AND r4.order_number=r.order_number AND r4.material_code=r.material_code AND r4.target_pph IS NOT NULL AND r4.target_pph>0 LIMIT 1) > 0
            AND SUM(COALESCE(net_working_minutes,0))>0 AND AVG(COALESCE(total_people,0))>0
          THEN ROUND((SUM(COALESCE(quantity,0)) / (SUM(COALESCE(net_working_minutes,0))/60.0 * AVG(COALESCE(total_people,0)))) / (SELECT target_pph FROM pph_records r5 WHERE r5.record_date=r.record_date AND r5.line_name=r.line_name AND r5.order_number=r.order_number AND r5.material_code=r.material_code AND r5.target_pph IS NOT NULL AND r5.target_pph>0 LIMIT 1) * 100, 2)
          WHEN (SELECT estimate_pph FROM pph_records r6 WHERE r6.record_date=r.record_date AND r6.line_name=r.line_name AND r6.order_number=r.order_number AND r6.material_code=r.material_code AND r6.estimate_pph IS NOT NULL AND r6.estimate_pph>0 LIMIT 1) > 0
            AND SUM(COALESCE(net_working_minutes,0))>0 AND AVG(COALESCE(total_people,0))>0
          THEN ROUND((SUM(COALESCE(quantity,0)) / (SUM(COALESCE(net_working_minutes,0))/60.0 * AVG(COALESCE(total_people,0)))) / (SELECT estimate_pph FROM pph_records r7 WHERE r7.record_date=r.record_date AND r7.line_name=r.line_name AND r7.order_number=r.order_number AND r7.material_code=r.material_code AND r7.estimate_pph IS NOT NULL AND r7.estimate_pph>0 LIMIT 1) * 100, 2)
          ELSE NULL
        END AS efficiency_rate
      FROM pph_records r
      WHERE r.record_date >= ? AND r.record_date <= ?
      GROUP BY r.record_date, r.line_name, r.order_number, r.material_code
      ORDER BY r.record_date, r.line_name`;

    const baseRows = await dbPool.query(baseQuery, [startDate, endDate]);

    let periods = [];
    if (gran==='week') periods = getWeeksByDateRange(startDate, endDate).map(w=>({ period:`W${w.week}`, startDate:w.startDate, endDate:w.endDate }));
    else if (gran==='month') periods = getMonthRangesByDate(startDate, endDate);
    else periods = getYearRangesByDate(startDate, endDate);

    const selectedLines = (line_names ? String(line_names).split(',').map(s=>s.trim()).filter(Boolean) : null);

    const rows = [];
    for (const p of periods) {
      let rowsIn = baseRows.filter(r => r.record_date >= p.startDate && r.record_date <= p.endDate);
      if (selectedLines && selectedLines.length) rowsIn = rowsIn.filter(r => selectedLines.includes(String(r.line_name||'')));

      if (dimension === 'line') {
        const byLine = new Map();
        for (const r of rowsIn) { const ln = r.line_name || '未知产线'; if (!byLine.has(ln)) byLine.set(ln, []); byLine.get(ln).push(r); }
        byLine.forEach((list, ln) => {
          const valid = list.filter(x => Number(x.target_pph) > 0 && x.efficiency_rate != null);
          const total_groups = list.length;
          const covered_groups = valid.length;
          const pass = valid.filter(x => Number(x.efficiency_rate) >= threshold_percent).length;
          const pass_rate = covered_groups>0 ? Math.round((pass*10000/covered_groups))/100 : 0;
          const coverage = total_groups>0 ? Math.round((covered_groups*10000/total_groups))/100 : 0;
          const avg_efficiency = covered_groups>0 ? Math.round((valid.reduce((s,x)=>s+Number(x.efficiency_rate||0),0)*100/covered_groups))/100 : 0;
          rows.push({ '周期': p.period, '生产线': ln, '总分组': total_groups, '有效分组(有目标)': covered_groups, '覆盖率%': coverage, '达标分组数': pass, '达标率%': pass_rate, '平均达成率%': avg_efficiency });
        });
      } else {
        const total_groups = rowsIn.length;
        const valid = rowsIn.filter(x => Number(x.target_pph) > 0 && x.efficiency_rate != null);
        const covered_groups = valid.length;
        const pass = valid.filter(x => Number(x.efficiency_rate) >= threshold_percent).length;
        const pass_rate = covered_groups>0 ? Math.round((pass*10000/covered_groups))/100 : 0;
        const coverage = total_groups>0 ? Math.round((covered_groups*10000/total_groups))/100 : 0;
        const avg_efficiency = covered_groups>0 ? Math.round((valid.reduce((s,x)=>s+Number(x.efficiency_rate||0),0)*100/covered_groups))/100 : 0;
        rows.push({ '周期': p.period, '总分组': total_groups, '有效分组(有目标)': covered_groups, '覆盖率%': coverage, '达标分组数': pass, '达标率%': pass_rate, '平均达成率%': avg_efficiency });
      }
    }

    if (!rows.length) {
      return res.status(404).json({ success: false, error: '没有数据可导出' });
    }

    const XLSX = require('xlsx');
    const workbook = XLSX.utils.book_new();
    const worksheet = XLSX.utils.json_to_sheet(rows);
    worksheet['!cols'] = (dimension==='line')
      ? [{wch:10},{wch:12},{wch:10},{wch:16},{wch:10},{wch:12},{wch:10},{wch:12}]
      : [{wch:10},{wch:10},{wch:16},{wch:10},{wch:12},{wch:10},{wch:12}];
    XLSX.utils.book_append_sheet(workbook, worksheet, '达标率分析');

    const labelMetric = metric==='avg_efficiency' ? '平均达成率' : '达标率';
    const filename = `达标率分析_${dimension==='line'?'按生产线':'总体'}_${gran}_${startDate}_${endDate}_${labelMetric}.xlsx`;
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`);
    res.setHeader('Content-Length', buffer.length);
    res.send(buffer);
  } catch (error) {
    console.error('efficiency-analysis export error:', error);
    res.status(500).json({ success: false, error: error.message });
  } finally {
    const ms = Date.now()-started;
    console.log(`[efficiency-analysis/export] ${ms}ms`);
  }
});


module.exports = {
  router,
  setDbPool
};
